﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Windows.Forms;
using AMS.Inventory.PDA.Castle.DataAccess;

namespace AMS.Inventory.PDA.Core.DataAccess
{
    internal class CoreDataAccess
    {
        private SqliteDBServer m_DbServer = new SqliteDBServer();
        private const string BASE_OBJECT_ID = "BaseObjectId";
         
        /// <summary>
        /// 资产信息表名,当前常用表名
        /// </summary>
        private string m_TableName = "AmsInventory_InventoryInfo";


        public void ConnectedDbClose()
        {
            m_DbServer.ConnectedDbClose();
        }

        /// <summary>
        /// 根据条码和计划编号获取资产表信息
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="planId"></param>
        /// <returns></returns>
        public DataTable GetAssetsInfoByCode(string barcode,string planId)
        {
            string sql;
            DataTable assetsDt;
            sql = ModCommon.AssetsSql+" where PlanId='"+planId+"' and Barcode='" + barcode + "' ";
            assetsDt = m_DbServer.GetDataTable(sql);
            return assetsDt;
        }

        private static string BARCODE = "Barcode", INVENTORYUSERINFO = "InventoryUserInfo";
        public string INVENTORY_STATE = "InventoryState", INVENTORYDATE = "InventoryDate", WAIT = "Wait", MATCH = "Match", ADD = "Add", UPDATED = "Updated";
        
        /// <summary>
        ///根据条码获取条码行信息
        /// </summary>
        /// <param name="strBarcode"></param>
        /// <param name="planId"></param>
        /// <returns></returns>
        public DataRow GetDataRowInventoryInfoByCode(string strBarcode,string planId,string userName)
        {
            DataTable dt = GetAssetsInfoByCode(strBarcode,planId);
            if (dt == null)
                new ArgumentException("DataTable不能为空");
            DataRow row = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                row = dt.Rows[0];
                //if (row[INVENTORY_STATE].ToString() == "Match" )
                //    row[INVENTORY_STATE]= "Updated"; else
                if (row[INVENTORY_STATE].ToString()== WAIT)
                  row[INVENTORY_STATE] =MATCH;
                else if (row[INVENTORY_STATE].ToString() ==ADD)
                  row[INVENTORY_STATE] = ADD; 
                row[INVENTORYDATE] =DateTime.Now.ToString(ModCommon.DateFomat);
            }
            else
            {
                row = dt.NewRow();
                row[BARCODE] = strBarcode;
 
                row[INVENTORY_STATE] = "Add";
            }
           
            row[INVENTORYUSERINFO] = userName;
            return row;
        }

        public int DeleteAssetsByCode(string barcode,string planId)
        {
            string sql = "";
            int factRow = 0;
            sql = "delete from AmsInventory_InventoryInfo where PlanId='"+planId+"' and Barcode='" + barcode + "' ";
            factRow = m_DbServer.ExecNoneQuery(sql);
            return factRow;
        }

        public int DeleteAssetsByCode(string barcode, string assetName, string planId)
        {
            string sql = "";
            int factRow = 0;
            sql = "delete from AmsInventory_InventoryInfo where PlanId='"+planId+"' and  Barcode='" + barcode + "' and Name='" + assetName + "' ";
            factRow = m_DbServer.ExecNoneQuery(sql);
            return factRow;
        }

        public int UpdateAssetsByCode(string upTitle, string upValue, string barcode, string planId)
        {
            int factRow = 0;
            string sql = "";
            string[] columNames = null, columValues = null;
            columNames = upTitle.Split(',');
            columValues = upValue.Split(',');
            for (int i = 0; i < columNames.Length; i++) //字段和字段值是一一对应的所以可以取任意数组的长度
            {
                sql += columNames[i] + "='" + columValues[i] + "',";
            }
            if (sql != "")
            {
                sql = "update " + m_TableName + " set " + sql.Substring(0, sql.Length - 1) + " where PlanId='"+planId+"' and Barcode='" + barcode +
                      "'";
                factRow = m_DbServer.ExecNoneQuery(sql);
            }
            else
            {
                return factRow;
            }

            return factRow;
        }

        public int UpdateAssetsByCode(Dictionary<string, string> assetInfoList, string barcode, string planId)
        {
            int factRow = 0;
            string sql = "";
            foreach (var s in assetInfoList)
            {
                sql += s.Key + "='" + s.Value+"',";
            }
            if (sql != "")
            {
                sql = "update " + m_TableName + " set " + sql.Substring(0, sql.Length - 1) + " where PlanId='"+planId+"' and Barcode='" + barcode +
                      "'";
                factRow = m_DbServer.ExecNoneQuery(sql);
            }
            else
            {
                return factRow;
            }

            return factRow;
        }

        /// <summary>
        /// 添加盘点新增资产信息
        /// </summary>
        /// <param name="upTitle">修改的数据库字段字符</param>
        /// <param name="upValue">修改的数据库字段值字符串</param>
        /// <returns></returns>
        public int AddAssets(string upTitle, string upValue)
        {
            int factRow = 0;
            string sql = "";
            string[] columValues;
            string columValueStr = "";
            columValues = upValue.Split(',');
            for (int i = 0; i < columValues.Length; i++)
            {
                columValueStr += "'" + columValues[i] + "',";
            }
            sql = "insert into " + m_TableName + "(" + upTitle + ") values(" + columValueStr.Substring(0,columValueStr.Length-1) + ")";
            factRow = m_DbServer.ExecNoneQuery(sql);
            return factRow;
        }

        /// <summary>
        /// 添加盘点新增资产信息
        /// </summary>
        /// <param name="addAssetInfoList">资产信息键值表，键代表数据表列名，值代表列对应的值</param>
        /// <returns></returns>
        public int AddAssets(Dictionary<string, string> addAssetInfoList)
        {
            int factRow = 0;
            string sql = "";
            string columName = "", columValue = "";
            foreach (var s in addAssetInfoList)
            {
                columName += s.Key + ",";
                columValue += s.Value + "','";
            }
            if (columName != "" && columValue != "") //
            {
                columName = columName.Substring(0, columName.Length - 1);
                columValue = columValue.Substring(0, columValue.Length - 2);
            }
            sql = "insert into " + m_TableName + "(" + columName + ") values(" + columValue + ")";
            factRow = m_DbServer.ExecNoneQuery(sql);
            return factRow;
        }


        /// <summary>
        /// 设置网格标题
        /// </summary>
        /// <param name="columnsTitle">DataGrid显示标题字符串</param>
        /// <param name="mySourceTable">DataGrid绑定的数据源</param>
        /// <param name="dgrdAssets">DataGrid控件对象</param>
        public static void SetColumnsTitle(string columnsTitle, DataTable mySourceTable, DataGrid dgrdAssets)
        {
            mySourceTable.TableName = "assetsTab";
            DataGridTableStyle _gTs;
            _gTs = new DataGridTableStyle();
            _gTs.MappingName = mySourceTable.TableName;
            string[] str1 = columnsTitle.Split(new[] {';'});
            foreach (string s in str1)
            {
                string[] str2 = s.Split(new[] {','});
                var dgt = new DataGridTextBoxColumn();
                dgt.MappingName = str2[0];
                dgt.HeaderText = str2[1];
                _gTs.GridColumnStyles.Add(dgt);
            }
            dgrdAssets.DataSource = null;
            if (dgrdAssets.TableStyles.Count > 0)
            {
                dgrdAssets.TableStyles[0].Dispose();
            }
            dgrdAssets.TableStyles.Clear();
            dgrdAssets.TableStyles.Add(_gTs);
            dgrdAssets.DataSource = mySourceTable;
        }

        public static string PLAN_ID = "PlanId",ID="Id";
        public void SaveDataRowToInventoryInfo(DataRow row,string keyProName, string strPlanId)
        {
            if (row != null)
            {
                StringBuilder sbInsertSqlColName = new StringBuilder();
                StringBuilder sbInsertSqlColVal = new StringBuilder();
                StringBuilder sbUpdateSql1 = new StringBuilder();
                bool isFirst = true;
                bool isAdd = false;
                string strKeyVal = "";

                foreach (DataColumn col in row.Table.Columns)
                {
                    if (isFirst)
                        isFirst = false;
                    else
                    {
                        sbInsertSqlColName.Append(",");
                        sbInsertSqlColVal.Append(",");
                        sbUpdateSql1.Append(",");
                    }
                    sbInsertSqlColName.Append(col.ColumnName);
                    string colVal = Convert.ToString(row[col.ColumnName]);
                    if (col.ColumnName == keyProName)
                    {
                        if(colVal.Length == 0)
                        {
                            isAdd = true;
                            colVal = Guid.NewGuid().ToString();
                        }
                        strKeyVal = colVal;
                    }
                    if (isAdd && col.ColumnName == PLAN_ID)
                    {
                        colVal = strPlanId;
                    }
                    if(col.DataType.Equals(typeof(Guid)) && colVal.Length==0)
                    {
                        colVal = Guid.NewGuid().ToString();
                    }
                    sbInsertSqlColVal.Append("'"+colVal+"'");
                    sbUpdateSql1.Append(col.ColumnName + "='" + colVal + "'");
                }

                string strExecSql = "Update " + m_TableName + " Set " + sbUpdateSql1 + " where " + PLAN_ID + "='" + strPlanId + "' and " + keyProName +
                                    "='" + strKeyVal + "'";
                if (isAdd)
                    strExecSql = "Insert into "+m_TableName+" (" + sbInsertSqlColName + ") values(" +
                                 sbInsertSqlColVal + ")";

                m_DbServer.ExecNoneQuery(strExecSql);
            }
        }
    }
}